package cn.kivensoft.xlsx.writer;

import java.io.FileOutputStream;
import java.io.OutputStream;
import java.text.SimpleDateFormat;
import java.time.LocalDate;
import java.time.LocalDateTime;
import java.time.ZoneId;
import java.util.ArrayList;
import java.util.Collections;
import java.util.Date;
import java.util.HashMap;
import java.util.HashSet;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;
import java.util.TimeZone;
import java.util.zip.ZipEntry;
import java.util.zip.ZipOutputStream;

import cn.kivensoft.xlsx.helper.MutiBuffer;
import cn.kivensoft.xlsx.writer.Style.Border.StyleValue;
import cn.kivensoft.xlsx.writer.Style.Fill;
import cn.kivensoft.xlsx.writer.Style.Font;
import cn.kivensoft.xlsx.writer.Workbook.OrganizeStyles;

public class FastLowLevel {
	private static final String XML_HEAD = "<?xml version=\"1.0\" encoding=\"utf-8\" standalone=\"yes\"?>";
	private static final String OPEN_XML_FORMATS = "http://schemas.openxmlformats.org";
	private static final long BASE_DAYS = LocalDate.of(1899, 12, 30).toEpochDay();

	private Workbook workbook;
	private ZipOutputStream zipout;
	private MutiBuffer mb;
	private int sharedStringsTotalCount;
	private LinkedHashMap<String, String> sharedStrings;

	public void save(Workbook workbook) throws Exception {
		try (FileOutputStream fos = new FileOutputStream(workbook.getFileName())) {
			save(workbook, fos);
		}
	}

	public void save(Workbook workbook, OutputStream output) throws Exception {
		this.workbook = workbook;
		workbook.resolveMergedCells();
		zipout = new ZipOutputStream(output);
		zipout.setMethod(ZipOutputStream.DEFLATED);
		zipout.setLevel(9);
		mb = new MutiBuffer();
		sharedStringsTotalCount = 0;
		sharedStrings = new LinkedHashMap<>();

		writeContentTypesXml();
		writeAppXml();
		writeCoreXml();
		writeGlobalRelsXml();

		writeWorkbookRelsXml();
		writeStylesXml();
		writeWorkbookXml();
		for (Worksheet sheet : workbook.getWorksheets())
			writeWorksheetXml(sheet);
		writeSharedStringsXml();

		zipout.flush();
		zipout.close();
	}

	private void writeContentTypesXml() throws Exception {
		zipout.putNextEntry(new ZipEntry("[Content_Types].xml"));

		mb.setLength(0).append(XML_HEAD).append("<Types xmlns=\"")
			.append(OPEN_XML_FORMATS).append("/package/2006/content-types\">")

			.append("<Default ContentType=\"")
			.append("application/vnd.openxmlformats-officedocument")
			.append(".spreadsheetml.sheet.main+xml")
			.append("\" Extension=\"").append("xml").append("\"/>")

			.append("<Default ContentType=\"")
			.append("application/vnd.openxmlformats")
			.append("-package.relationships+xml")
			.append("\" Extension=\"").append("rels").append("\"/>")

			.append("<Override ContentType=\"")
			.append("application/vnd.openxmlformats")
			.append("-package.core-properties+xml")
			.append("\" PartName=\"")
			.append("/docProps/core.xml").append("\"/>")

			.append("<Override ContentType=\"")
			.append("application/vnd.openxmlformats-officedocument")
			.append(".extended-properties+xml")
			.append("\" PartName=\"")
			.append("/docProps/app.xml").append("\"/>")

			.append("<Override ContentType=\"")
			.append("application/vnd.openxmlformats-officedocument")
			.append(".spreadsheetml.sharedStrings+xml")
			.append("\" PartName=\"")
			.append("/xl/sharedStrings.xml").append("\"/>")

			.append("<Override ContentType=\"")
			.append("application/vnd.openxmlformats-officedocument")
			.append(".spreadsheetml.styles+xml")
			.append("\" PartName=\"")
			.append("/xl/styles.xml").append("\"/>");


		List<Worksheet> sheets = workbook.getWorksheets();
		for (int i = 0, imax = sheets.size(); i < imax; ++i)
			mb.append("<Override ContentType=\"")
				.append("application/vnd.openxmlformats-officedocument")
				.append(".spreadsheetml.worksheet+xml")
				.append("\" PartName=\"")
				.append("/xl/worksheets/sheet")
				.append(Integer.toString(sheets.get(i).getSheetID()))
				.append(".xml")
				.append("\"/>");

		mb.append("</Types>");
		write(zipout, mb);
	}

	private void writeAppXml() throws Exception {
		zipout.putNextEntry(new ZipEntry("docProps/app.xml"));

		mb.setLength(0).append(XML_HEAD)
			.append("<Properties xmlns=\"").append(OPEN_XML_FORMATS)
			.append("/officeDocument/2006/extended-properties\"")
			.append("xmlns:vt=\"").append(OPEN_XML_FORMATS)
			.append("/officeDocument/2006/docPropsVTypes\">");

		addTag(mb, "TotalTime", "0");
		addTag(mb, "DocSecurity", "0");
		addTag(mb, "ScaleCrop", "false");
		addTag(mb, "LinksUpToDate", "false");
		addTag(mb, "SharedDoc", "false");
		addTag(mb, "HyperlinksChanged", "false");

		Metadata md = workbook.getWorkbookMetadata();
		if (md != null) {
			addTag(mb, "Application", md.application);
			addTag(mb, "Manager", md.manager);
			addTag(mb, "Company", md.company);
			addTag(mb, "HyperlinkBase", md.hyperlinkBase);
			addTag(mb, "AppVersion", md.applicationVersion);
		}

		mb.append("</Properties>");
		write(zipout, mb);
	}

	private void writeCoreXml() throws Exception {
		zipout.putNextEntry(new ZipEntry("docProps/core.xml"));

		mb.setLength(0).append(XML_HEAD)
			.append("<cp:coreProperties xmlns:cp=\"")
			.append(OPEN_XML_FORMATS).append("/package/2006/metadata/core-properties\"")
			.append(" xmlns:dc=\"http://purl.org/dc/elements/1.1/\"")
			.append(" xmlns:dcterms=\"http://purl.org/dc/terms/\"")
			.append(" xmlns:dcmitype=\"http://purl.org/dc/dcmitype/\"")
			.append(" xmlns:xsi=\"http://www.w3.org/2001/XMLSchema-instance\">");

    	SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd'T'HH:mm:ss'Z'");
    	sdf.setTimeZone(TimeZone.getTimeZone("GMT"));
    	String now = sdf.format(new Date());
		addTag(mb, "dcterms:created", "xsi:type=\"dcterms:W3CDTF\"", now);
		addTag(mb, "dcterms:modified", "xsi:type=\"dcterms:W3CDTF\"", now);

		Metadata md = workbook.getWorkbookMetadata();
		if (md != null) {
			addTag(mb, "dc:title", md.title);
			addTag(mb, "dc:subject", md.subject);
			addTag(mb, "dc:creator", md.creator);
			addTag(mb, "dc:description", md.description);
			addTag(mb, "cp:lastModifiedBy", md.creator);
			addTag(mb, "cp:keywords", md.keywords);
			addTag(mb, "cp:category", md.category);
			addTag(mb, "cp:contentStatus", md.contentStatus);
		}

		mb.append("</cp:coreProperties>");
		write(zipout, mb);
	}

	private void writeGlobalRelsXml() throws Exception {
		zipout.putNextEntry(new ZipEntry("_rels/.rels"));
		int id = 0;

		mb.setLength(0).append(XML_HEAD)
			.append("<Relationships xmlns=\"")
			.append(OPEN_XML_FORMATS).append("/package/2006/relationships\">");

		addRelsNode(mb, ++id, "/xl/workbook.xml",
				"/officeDocument/2006/relationships/officeDocument");
		addRelsNode(mb, ++id, "/docProps/core.xml",
				"/package/2006/relationships/metadata/core-properties");
		addRelsNode(mb, ++id, "/docProps/app.xml",
				"/officeDocument/2006/relationships/extended-properties");

		mb.append("</Relationships>");
		write(zipout, mb);
	}

	private void writeWorkbookRelsXml() throws Exception {
		zipout.putNextEntry(new ZipEntry("xl/_rels/workbook.xml.rels"));
		List<Worksheet> sheets = workbook.getWorksheets();
		int id = 0;

		mb.setLength(0).append(XML_HEAD)
			.append("<Relationships xmlns=\"")
			.append(OPEN_XML_FORMATS).append("/package/2006/relationships\">");

		for (int i = 0, imax = sheets.size(); i < imax; ++i)
			addRelsNode(mb, ++id, sheets.get(i).getSheetID());

		addRelsNode(mb, ++id, "/xl/styles.xml",
				"/officeDocument/2006/relationships/styles");
		addRelsNode(mb, ++id, "/xl/sharedStrings.xml",
				"/officeDocument/2006/relationships/sharedStrings");

		mb.append("</Relationships>");
		write(zipout, mb);
	}

	private void writeWorksheetXml(Worksheet sheet) throws Exception {
		zipout.putNextEntry(new ZipEntry("xl/worksheets/sheet" + sheet.getSheetID() + ".xml"));
		sheet.recalculateAutoFilter();
		sheet.recalculateColumns();
		List<List<Cell>> celldata = getSortedSheetData(sheet);

		mb.setLength(0).append(XML_HEAD).append("<worksheet xmlns=\"").append(OPEN_XML_FORMATS)
				.append("/spreadsheetml/2006/main")
				.append("\" xmlns:mc=\"").append(OPEN_XML_FORMATS)
				.append("/markup-compatibility/2006").append("\">");

		if (sheet.getSelectedCells() != null) {
			mb.append("<sheetViews><sheetView workbookViewId=\"0\"");
			if (workbook.getSelectedWorksheet() == sheet.getSheetID() - 1)
				mb.append(" tabSelected=\"1\"");
			mb.append("><selection sqref=\"");
			mb.append(sheet.getSelectedCells().toString());
			mb.append("\" activeCell=\"");
			mb.append(sheet.getSelectedCells().startAddress.toString());
			mb.append("\"/></sheetView></sheetViews>");
		}

		mb.append("<sheetFormatPr defaultRowHeight=\"")
				.append(Float.toString(sheet.getDefaultRowHeight()))
				.append("\" baseColWidth=\"")
				.append(Float.toString(sheet.getDefaultColumnWidth())).append("\"/>");

		createColsString(mb, sheet);

		mb.append("<sheetData>");
		for (List<Cell> item : celldata)
			createRowString(mb, item, sheet);
		mb.append("</sheetData>");

		createMergedCellsString(mb, sheet);
		createSheetProtectionString(mb, sheet);
		if (sheet.getAutoFilterRange() != null)
			mb.append("<autoFilter ref=\"")
					.append(sheet.getAutoFilterRange().toString())
					.append("\"/>");

		mb.append("</worksheet>");
		write(zipout, mb);
	}

	/** 解析工作表中的共享字符串 */
	private List<List<Cell>> getSortedSheetData(Worksheet sheet) {
		ArrayList<Cell> temp = new ArrayList<>();
		for (Map.Entry<String, Cell> item : sheet.getCells().entrySet())
			temp.add(item.getValue());
		Collections.sort(temp);

		List<Cell> line = new ArrayList<>();
		List<List<Cell>> ret = new ArrayList<>();
		if (temp.size() > 0) {
			int rowNumber = temp.get(0).rowAddress;
			for (Cell item : temp) {
				if (item.rowAddress != rowNumber) {
					ret.add(line);
					line = new ArrayList<>();
					rowNumber = item.rowAddress;
				}
				line.add(item);
			}
			if (line.size() > 0) ret.add(line);
		}

		return ret;
	}

	/**
	 * 创建一行数据字符串
	 *
	 * @param columns 单元格列表
	 * @param sheet 工作表
	 * @return 格式化后的字符串
	 */
	private void createRowString(MutiBuffer mb, List<Cell> columns, Worksheet sheet) {
		int rowNumber = columns.get(0).rowAddress;
		String heigth = "";
		String hidden = "";

		Map<Integer, Float> row_heights = sheet.getRowHeights();
		if (row_heights.containsKey(rowNumber)
				&& !equals(row_heights.get(rowNumber), sheet.getDefaultRowHeight()))
			heigth = " customHeight=\"1\" ht=\"" + row_heights.get(rowNumber) + "\"";

		Map<Integer, Boolean> hiddens = sheet.getHiddenRows();
		if (hiddens.containsKey(rowNumber) && hiddens.get(rowNumber))
			hidden = " hidden=\"1\"";

		if (columns.size() > 0)
			mb.append("<row r=\"").append(Integer.toString(rowNumber + 1)).append("\"")
					.append(heigth).append(hidden).append(">");
		else mb.append("<row").append(heigth).append(">");

		// String typeAttribute;
		String sValue;
		String tValue;
		String value = "";
		for (Cell item : columns) {
			tValue = " ";
			if (item.cellStyle != null) sValue = " s=\"" + item.cellStyle.internalID + "\" ";
			else sValue = "";
			item.resolveCellType();
			Cell.CellType _ct = item.fieldtype;
			// Recalculate the type (for handling DEFAULT)
			if (_ct == Cell.CellType.BOOL) {
				tValue = " t=\"b\" ";
				value = (Boolean) item.value ? "1" : "0";
			}
			// Number casting
			else if (_ct == Cell.CellType.NUMBER) {
				tValue = " t=\"n\" ";
				value = ((Number) item.value).toString();
			}
			// Date parsing
			else if (_ct == Cell.CellType.DATE) {
				value = Double.toString(getOADate((Date) item.value));
			} else {
				// String parsing
				if (item.value == null) {
					tValue = " t=\"str\" ";
					value = "";
				} else {
					// Handle sharedStrings
					if (_ct == Cell.CellType.FORMULA) {
						tValue = " t=\"str\" ";
						value = item.value.toString();
					} else {
						tValue = " t=\"s\" ";
						value = item.value.toString();
						if (!sharedStrings.containsKey(value))
							sharedStrings.put(value, Integer.toString(sharedStrings.size()));

						value = sharedStrings.get(value);
						sharedStringsTotalCount++;
					}
				}
			}
			if (_ct != Cell.CellType.EMPTY) {
				mb.append("<c").append(tValue).append("r=\"")
						.append(item.getCellAddress()).append("\"")
						.append(sValue).append(">");
				if (_ct == Cell.CellType.FORMULA)
					mb.append("<f>").appendXmlValue(item.value.toString()).append("</f>");
				else
					mb.append("<v>").appendXmlValue(value).append("</v>");
				mb.append("</c>");
			} else {
				// Empty cell
				mb.append("<c").append(tValue).append("r=\"")
						.append(item.getCellAddress()).append("\"")
						.append(sValue).append("/>");
			}
		}
		mb.append("</row>");
	}

	/** 创建列宽定义字符串 */
	private void createColsString(MutiBuffer mb, Worksheet sheet) {
		Map<Integer, Worksheet.Column> cols = sheet.getColumns();
		if (cols.size() == 0) return;
		boolean finded = false;
		String hidden;
		float def_width = sheet.getDefaultColumnWidth();

		for (Map.Entry<Integer, Worksheet.Column> item : cols.entrySet()) {
			Worksheet.Column col = item.getValue();
			if (equals(col.getWidth(), def_width) && !col.getIsHidden())
				continue;

			if (cols.containsKey(item.getKey()) && cols.get(item.getKey()).getIsHidden())
				hidden = " hidden=\"1\"";
			else hidden = "";

			if (!finded) {
				finded = true;
				mb.append("<cols>");
			}

			String col_idx = Integer.toString(item.getKey() + 1);
			mb.append("<col customWidth=\"1\" width=\"")
					.append(Float.toString(col.getWidth())).append("\" max=\"")
					.append(col_idx).append("\" min=\"")
					.append(col_idx).append("\"").append(hidden).append("/>");
		}
		if (finded) mb.append("</cols>");
	}

	/** 创建合并单元格字符串
	 * @param sheet 工作表
	 * @return 格式化后的合并单元格字符串
	 */
	private static void createMergedCellsString(MutiBuffer mb, Worksheet sheet) {
		if (sheet.getMergedCells().size() < 1) return;
		mb.append("<mergeCells count=\"")
				.append(Integer.toString(sheet.getMergedCells().size()))
				.append("\">");
		for (Map.Entry<String, Cell.Range> item : sheet.getMergedCells().entrySet())
			mb.append("<mergeCell ref=\"")
					.append(item.getValue().toString())
					.append("\"/>");
		mb.append("</mergeCells>");
	}

	/** 创建工作表属性 */
	private static void createSheetProtectionString(MutiBuffer mb, Worksheet sheet) {
		if (!sheet.getUseSheetProtection()) return;

		Map<Worksheet.SheetProtectionValue, String> actLockVals = new HashMap<>();
		List<Worksheet.SheetProtectionValue> _spv = sheet.getSheetProtectionValues();

		if (_spv.size() == 0) {
			actLockVals.put(Worksheet.SheetProtectionValue.selectLockedCells, "1");
			actLockVals.put(Worksheet.SheetProtectionValue.selectUnlockedCells, "1");
		}

		if (!_spv.contains(Worksheet.SheetProtectionValue.objects))
			actLockVals.put(Worksheet.SheetProtectionValue.objects, "1");

		if (!_spv.contains(Worksheet.SheetProtectionValue.scenarios))
			actLockVals.put(Worksheet.SheetProtectionValue.scenarios, "1");

		if (!_spv.contains(Worksheet.SheetProtectionValue.selectLockedCells)) {
			if (!actLockVals.containsKey(Worksheet.SheetProtectionValue.selectLockedCells))
				actLockVals.put(Worksheet.SheetProtectionValue.selectLockedCells, "1");
		}

		if (!_spv.contains(Worksheet.SheetProtectionValue.selectUnlockedCells)
				|| !_spv.contains(Worksheet.SheetProtectionValue.selectLockedCells)) {
			if (!actLockVals
					.containsKey(Worksheet.SheetProtectionValue.selectUnlockedCells))
				actLockVals.put(Worksheet.SheetProtectionValue.selectUnlockedCells, "1");
		}

		if (_spv.contains(Worksheet.SheetProtectionValue.formatCells))
			actLockVals.put(Worksheet.SheetProtectionValue.formatCells, "0");

		if (_spv.contains(Worksheet.SheetProtectionValue.formatColumns))
			actLockVals.put(Worksheet.SheetProtectionValue.formatColumns, "0");

		if (_spv.contains(Worksheet.SheetProtectionValue.formatRows))
			actLockVals.put(Worksheet.SheetProtectionValue.formatRows, "0");

		if (_spv.contains(Worksheet.SheetProtectionValue.insertColumns))
			actLockVals.put(Worksheet.SheetProtectionValue.insertColumns, "0");

		if (_spv.contains(Worksheet.SheetProtectionValue.insertRows))
			actLockVals.put(Worksheet.SheetProtectionValue.insertRows, "0");

		if (_spv.contains(Worksheet.SheetProtectionValue.insertHyperlinks))
			actLockVals.put(Worksheet.SheetProtectionValue.insertHyperlinks, "0");

		if (_spv.contains(Worksheet.SheetProtectionValue.deleteColumns))
			actLockVals.put(Worksheet.SheetProtectionValue.deleteColumns, "0");

		if (_spv.contains(Worksheet.SheetProtectionValue.deleteRows))
			actLockVals.put(Worksheet.SheetProtectionValue.deleteRows, "0");

		if (_spv.contains(Worksheet.SheetProtectionValue.sort))
			actLockVals.put(Worksheet.SheetProtectionValue.sort, "0");

		if (_spv.contains(Worksheet.SheetProtectionValue.autoFilter))
			actLockVals.put(Worksheet.SheetProtectionValue.autoFilter, "0");

		if (_spv.contains(Worksheet.SheetProtectionValue.pivotTables))
			actLockVals.put(Worksheet.SheetProtectionValue.pivotTables, "0");

		mb.append("<sheetProtection");
		for (Map.Entry<Worksheet.SheetProtectionValue, String> item : actLockVals.entrySet())
			mb.append(" ").append(item.getKey().name()).append("=\"")
					.append(item.getValue()).append("\"");

		String _spp = sheet.getSheetProtectionPassword();
		if (_spp != null && _spp.length() > 0) {
			byte[] hash = generatePasswordHash(sheet.getSheetProtectionPassword());
			mb.append(" password=\"").append(hash).append("\"");
		}

		mb.append(" sheet=\"1\"/>");
	}

	private void writeSharedStringsXml() throws Exception {
		zipout.putNextEntry(new ZipEntry("xl/sharedStrings.xml"));

		mb.setLength(0).append(XML_HEAD)
			.append("<sst xmlns=\"").append(OPEN_XML_FORMATS)
				.append("/spreadsheetml/2006/main\"")
				.append(" count=\"").append(Integer.toString(sharedStringsTotalCount))
				.append("\" uniqueCount=\"").append(Integer.toString(sharedStrings.size()))
				.append("\">");
		for (Map.Entry<String, String> item : sharedStrings.entrySet()) {
			mb.append("<si><t>").appendXmlValue(item.getKey())
					.append("</t></si>");
		}
		mb.append("</sst>");
		write(zipout, mb);
	}

	private void writeWorkbookXml() throws Exception {
		zipout.putNextEntry(new ZipEntry("xl/workbook.xml"));

		mb.setLength(0).append(XML_HEAD)
			.append("<workbook xmlns=\"")
			.append(OPEN_XML_FORMATS).append("/spreadsheetml/2006/main\"")
			.append(" xmlns:r=\"")
			.append(OPEN_XML_FORMATS).append("/officeDocument/2006/relationships\">");

		if (workbook.getSelectedWorksheet() > 0)
			mb.append("<bookViews><workbookView activeTab=\"")
					.append(Integer.toString(workbook.getSelectedWorksheet()))
					.append("\"/></bookViews>");

		if (workbook.getUseWorkbookProtection()) {
			mb.append("<workbookProtection");
			if (workbook.getLockWindowsIfProtected()) mb.append(" lockWindows=\"1\"");
			if (workbook.getLockStructureIfProtected()) mb.append(" lockStructure=\"1\"");
			if (workbook.getWorkbookProtectionPassword() != null
					&& workbook.getWorkbookProtectionPassword().length() > 0)
				mb.append("workbookPassword=\"")
						.append(generatePasswordHash(workbook.getWorkbookProtectionPassword()))
						.append("\"");

			mb.append("/>");
		}

		mb.append("<sheets>");
		for (Worksheet sheet : workbook.getWorksheets()) {
			String id = Integer.toString(sheet.getSheetID());
			mb.append("<sheet r:id=\"rId").append(id)
				.append("\" sheetId=\"").append(id)
				.append("\" name=\"").appendXmlAttr(sheet.getSheetName())
				.append("\"/>");
		}
		mb.append("</sheets>");

		mb.append("</workbook>");
		write(zipout, mb);
	}

	public static byte[] generatePasswordHash(String password) {
		if (password == null || password.isEmpty()) return null;

		int pwLen = password.length();
		int pwHash = 0;
		for (int i = pwLen; i > 0; i--) {
			char c = password.charAt(i - 1);
			pwHash = ((pwHash >> 14) & 0x01) | ((pwHash << 1) & 0x7fff);
			pwHash ^= c;
		}
		pwHash = ((pwHash >> 14) & 0x01) | ((pwHash << 1) & 0x7fff);
		pwHash ^= (0x8000 | ('N' << 8) | 'K');
		pwHash ^= pwLen;

		byte[] buf = new byte[8];
		return getHex(pwHash, buf);
	}

	private void writeStylesXml() throws Exception {
		zipout.putNextEntry(new ZipEntry("xl/styles.xml"));

		mb.setLength(0).append(XML_HEAD)
			.append("<styleSheet xmlns=\"")
			.append(OPEN_XML_FORMATS).append("/spreadsheetml/2006/main\"")
			.append(" xmlns:mc=\"")
			.append(OPEN_XML_FORMATS).append("/markup-compatibility/2006\">");

		OrganizeStyles styles = workbook.reorganizeStyles();
		addStyleNumberFormats(mb, styles.numberFormats);
		addStyleFonts(mb, styles.fonts);
		addStyleFills(mb, styles.fills);
		addStyleBorders(mb, styles.borders);
		addStyleCellXfs(mb, workbook.getStyles());
		Metadata md = workbook.getWorkbookMetadata();
		if (md != null && (md.useColorMRU || md.useIndexColor))
			addStyleColors(mb, styles.fonts, styles.fills, md.useIndexColor);

		mb.append("</styleSheet>");
		write(zipout, mb);
	}

	private static void addStyleNumberFormats(MutiBuffer mb, List<Style.NumberFormat> numFmts) {
		boolean finded = false;
		for (Style.NumberFormat nf : numFmts) {
			if (nf.isCustomFormat()) {
				if (!finded) {
					finded = true;
					mb.append("<numFmts count=\"").append(Integer.toString(numFmts.size()))
						.append("\">");
				}
				mb.append("<numFmt formatCode=\"")
						.append(nf.customFormatCode)
						.append("\" numFmtId=\"")
						.append(Integer.toString(nf.customFormatID))
						.append("\"/>");
			}
		}
		if (finded) mb.append("</numFmts>");
	}

	private static void addStyleFonts(MutiBuffer mb, List<Style.Font> fonts) {
		if (fonts.size() == 0) return;
		mb.append("<fonts count=\"").append(Integer.toString(fonts.size())).append("\">");

		for (Style.Font font : fonts) {
			mb.append("<font>");

			if (font.bold) mb.append("<b/>");
			if (font.italic) mb.append("<i/>");
			if (font.underline) mb.append("<u/>");
			if (font.doubleUnderline) mb.append("<u val=\"double\"/>");
			if (font.strike) mb.append("<strike/>");

			if (font.verticalAlign == Style.Font.VerticalAlignValue.subscript)
				mb.append("<vertAlign val=\"subscript\"/>");
			else if (font.verticalAlign == Style.Font.VerticalAlignValue.superscript)
				mb.append("<vertAlign val=\"superscript\"/>");

			mb.append("<sz val=\"").append(Integer.toString(font.size)).append("\"/>");

			String _cv = font.colorValue;
			if (_cv == null || _cv.length() == 0) mb.append("<color indexed=\"8\"/>");
			else mb.append("<color rgb=\"").append(_cv).append("\"/>");

			mb.append("<name val=\"").append(font.name).append("\"/>");
			mb.append("<charset val=\"134\"/>");

			String _cs = font.charset;
			if (_cs != null && _cs.length() > 0)
				mb.append("<charset val=\"").append(_cs).append("\"/>");

			mb.append("</font>");
		}

		mb.append("</fonts>");
	}

	private static void addStyleFills(MutiBuffer mb, List<Style.Fill> fills) {
		if (fills.size() == 0) return;
		mb.append("<fills count=\"").append(Integer.toString(fills.size())).append("\">");
		for (Style.Fill fill : fills) {
			mb.append("<fill>").append("<patternFill patternType=\"")
					.append(Style.Fill.getPatternName(fill.patternFill))
					.append('"');

			Style.Fill.PatternValue pf = fill.patternFill;
			if (pf == Style.Fill.PatternValue.solid)
				mb.append('>').append("<fgColor rgb=\"")
						.append(fill.foregroundColor).append("\"/>")
						.append("<bgColor indexed=\"")
						.append(Integer.toString(fill.indexedColor)).append("\"/>")
						.append("</patternFill>");
			else if (pf == Style.Fill.PatternValue.mediumGray
					|| pf == Style.Fill.PatternValue.lightGray
					|| pf == Style.Fill.PatternValue.gray0625
					|| pf == Style.Fill.PatternValue.darkGray) {
				mb.append('>').append("<fgColor rgb=\"").append(fill.foregroundColor).append("\"/>");

				String _bc = fill.backgroundColor;
				if (_bc != null && _bc.length() > 0)
					mb.append("<bgColor rgb=\"").append(fill.backgroundColor).append("\"/>");

				mb.append("</patternFill>");
			} else {
				mb.append("/>");
			}
			mb.append("</fill>");
		}
		mb.append("</fills>");
	}

	private static void addStyleBorders(MutiBuffer mb, List<Style.Border> borders) {
		if (borders.size() == 0) return;
		mb.append("<borders count=\"").append(Integer.toString(borders.size())).append("\">");
		for (Style.Border item : borders) {
			if (item.diagonalDown && item.diagonalUp)
				mb.append("<border diagonalDown=\"1\" diagonalUp=\"1\">");
			else if (item.diagonalDown) mb.append("<border diagonalDown=\"1\">");
			else if (item.diagonalUp) mb.append("<border diagonalUp=\"1\">");
			else mb.append("<border>");

			addStyleBorder(mb, item.leftStyle, "left", item.leftColor);
			addStyleBorder(mb, item.rightStyle, "right", item.rightColor);
			addStyleBorder(mb, item.topStyle, "top", item.topColor);
			addStyleBorder(mb, item.bottomStyle, "bottom", item.bottomColor);

			addStyleBorder(mb, item.diagonalStyle, "diagonal", item.diagonalColor);

			mb.append("</border>");
		}
		mb.append("</borders>");
	}

	private static void addStyleBorder(MutiBuffer mb, StyleValue sv, String name, String color) {
		if (sv != Style.Border.StyleValue.none) {
			mb.append('<').append(name).append(" style=\"")
					.append(Style.Border.getStyleName(sv)).append("\">");
			if (color != null && color.length() > 0)
				mb.append("<color rgb=\"").append(color).append("\"/>");
			else mb.append("<color auto=\"1\"/>");
			mb.append('<').append('/').append(name).append('>');
		} else mb.append('<').append(name).append('/').append('>');
	}

	private static void addStyleCellXfs(MutiBuffer mb, List<Style> styles) {
		if (styles.size() == 0) return;
		int textRotation;
		mb.append("<cellXfs count=\"").append(Integer.toString(styles.size())).append("\">");
		for (Style item : styles) {
			Style.CellXf _cx = item.currentCellXf;
			textRotation = _cx.calculateInternalRotation();
			boolean isAlignment = _cx.horizontalAlign != Style.CellXf.HorizontalAlignValue.none
					|| _cx.verticalAlign != Style.CellXf.VerticallAlignValue.none
					|| _cx.alignment != Style.CellXf.TextBreakValue.none
					|| textRotation != 0;
			boolean isProtection = _cx.hidden || _cx.locked;

			Style.NumberFormat _nf = item.currentNumberFormat;
			mb.append("<xf numFmtId=\"")
				.append(Integer.toString(_nf.isCustomFormat() ? _nf.customFormatID : _nf.number));
			mb.append("\" borderId=\"").append(Integer.toString(item.currentBorder.internalID));
			mb.append("\" fillId=\"").append(Integer.toString(item.currentFill.internalID));
			mb.append("\" fontId=\"").append(Integer.toString(item.currentFont.internalID));
			if (!item.currentFont.isDefaultFont()) mb.append("\" applyFont=\"1");
			if (item.currentFill.patternFill != Style.Fill.PatternValue.none)
				mb.append("\" applyFill=\"1");
			if (!item.currentBorder.isEmpty()) mb.append("\" applyBorder=\"1");
			if (isAlignment || _cx.forceApplyAlignment)
				mb.append("\" applyAlignment=\"1");
			if (isProtection) mb.append("\" applyProtection=\"1");
			if (item.currentNumberFormat.number != Style.NumberFormat.FORMAT_NONE)
				mb.append("\" applyNumberFormat=\"1");
			mb.append('"');

			if (isAlignment || isProtection) {
				mb.append(">");
				if (isAlignment) {
					mb.append("<alignment");
					Style.CellXf.HorizontalAlignValue _hav = _cx.horizontalAlign;
					if (_hav != Style.CellXf.HorizontalAlignValue.none)
						mb.append(" horizontal=\"").append(_hav.name()).append('"');
					Style.CellXf.VerticallAlignValue _vav = _cx.verticalAlign;
					if (_vav != Style.CellXf.VerticallAlignValue.none)
						mb.append(" vertical=\"").append(_vav.name()).append('"');
					if (_cx.alignment != Style.CellXf.TextBreakValue.none)
						mb.append(' ').append(_cx.alignment.name()).append("=\"1\"");
					if (textRotation != 0)
						mb.append(" textRotation=\"").append(Integer.toString(textRotation))
								.append("\"");
					mb.append("/>");
				}
				if (isProtection) {
					mb.append("<protection locked=\"")
						.append(_cx.locked ? "1" : "0")
						.append("\" hidden=\"")
						.append(_cx.hidden ? "1" : "0")
						.append("\"/>");
				}
				mb.append("</xf>");
			} else mb.append("/>");
		}
		mb.append("</cellXfs>");
	}

	private static void addStyleColors(MutiBuffer mb, List<Style.Font> fonts,
			List<Style.Fill> fills, boolean useIndexColor) {

		HashSet<String> cs = new HashSet<>();
		for (Font item : fonts) addIfNotDefaultColor(cs, item.colorValue);
		for (Fill item : fills) {
			addIfNotDefaultColor(cs, item.backgroundColor);
			addIfNotDefaultColor(cs, item.foregroundColor);
		}

		if (cs.isEmpty() && !useIndexColor) return;

		mb.append("<colors>");
		if (useIndexColor) addIndexColors(mb);
		if (cs.size() > 0) {
			mb.append("<mruColors>");
			for (String s : cs)
				mb.append("<color rgb=\"").append(s).append("\"/>");
			mb.append("</mruColors>");
		}
		mb.append("</colors>");
	}

	private static void addIfNotDefaultColor(HashSet<String> hs, String color) {
		if (color != null && color.length() > 0 && !Fill.DEFAULTCOLOR.equals(color))
			hs.add(color);
	}

	private static final int[] INDEX_COLORS = { 0x00000000, 0x00FFFFFF, 0x00FF0000, 0x0000FF00,
			0x000000FF, 0x00FFFF00, 0x00FF00FF, 0x0000FFFF, 0x00000000, 0x00FFFFFF, 0x00FF0000,
			0x0000FF00, 0x000000FF, 0x00FFFF00, 0x00FF00FF, 0x0000FFFF, 0x00800000, 0x00008000,
			0x00000080, 0x00808000, 0x00800080, 0x00008080, 0x00C0C0C0, 0x00808080, 0x009999FF,
			0x00993366, 0x00FFFFCC, 0x00CCFFFF, 0x00660066, 0x00FF8080, 0x000066CC, 0x00CCCCFF,
			0x00000080, 0x00FF00FF, 0x00FFFF00, 0x0000FFFF, 0x00800080, 0x00800000, 0x00008080,
			0x000000FF, 0x0000CCFF, 0x00CCFFFF, 0x00CCFFCC, 0x00FFFF99, 0x0099CCFF, 0x00DCDCDC,
			0x00CC99FF, 0x00F2F2F2, 0x003366FF, 0x0033CCCC, 0x0099CC00, 0x00FFCC00, 0x00FF9900,
			0x00FF6600, 0x00666699, 0x00969696, 0x00003366, 0x00339966, 0x00003300, 0x00333300,
			0x00993300, 0x00993366, 0x00333399, 0x00333333 };
	private static void addIndexColors(MutiBuffer mb) {
		byte[] bs = new byte[8];
		mb.append("<indexedColors>");
		for (int i = 0, imax = INDEX_COLORS.length; i < imax; ++i)
			mb.append("<rgbColor rgb=\"").append(getHex(INDEX_COLORS[i], bs)).append("\"/>");
		mb.append("</indexedColors>");
	}

	private static void addRelsNode(MutiBuffer mb, int id, String target, String type) {
		mb.append("<Relationship Id=\"rId").append(Integer.toString(id))
			.append("\" Target=\"").append(target)
			.append("\" Type=\"").append(OPEN_XML_FORMATS).append(type)
			.append("\"/>");
	}

	private static void addRelsNode(MutiBuffer mb, int id, int sheetId) {
		mb.append("<Relationship Id=\"rId").append(Integer.toString(id))
			.append("\" Target=\"/xl/worksheets/sheet")
			.append(Integer.toString(sheetId)).append(".xml")
			.append("\" Type=\"").append(OPEN_XML_FORMATS)
			.append("/officeDocument/2006/relationships/worksheet")
			.append("\"/>");
	}

	private static void addTag(MutiBuffer mb, String tag, String attr, String value) {
		mb.append('<').append(tag).append(' ').append(attr).append('>');
		if (value != null) mb.appendXmlValue(value);
		mb.append('<').append('/').append(tag).append('>');
	}

	private static void addTag(MutiBuffer mb, String tag, String value) {
		mb.append('<').append(tag).append('>');
		if (value != null) mb.appendXmlValue(value);
		mb.append('<').append('/').append(tag).append('>');
	}

	private static byte[] getHex(int x, byte[] buf) {
		int n;
		for (int i = 0; i < 8; ++i) {
			n = (x >> ((7 - i) << 2)) & 0x0F;
			buf[i] = n < 10 ? (byte) (48 + n) : (byte) (55 + n);
		}
		return buf;
	}

	/** 转化C#时间为Excel时间格式（OLE格式） */
	public double getOADate(Date date) {
		LocalDateTime ldt = date.toInstant().atZone(ZoneId.systemDefault()).toLocalDateTime();
		return (ldt.toLocalDate().toEpochDay() - BASE_DAYS)
				+ ((double) ldt.toLocalTime().toSecondOfDay() / 86400);
		// calendar.setTime(date);
		// long t = calendar.getTimeInMillis();
		// double span = t - BASE_MILLIS;
		// double days = Math.floor(span / (1000 * 24 * 60 * 60));
		// double h = calendar.get(Calendar.HOUR_OF_DAY);
		// double m = calendar.get(Calendar.MINUTE);
		// double s = calendar.get(Calendar.SECOND);
		// return ((s + (m * 60) + (h * 3600)) / 86400) + days;
	}

	private static boolean equals(float a, float b) {
		return equals(a, b, 0.0000001f);
	}

	private static boolean equals(float a, float b, float eps) {
		return a == b || Math.abs(a - b) < eps;
	}

	private static void write(ZipOutputStream zip, MutiBuffer mb) throws Exception {
		Exception[] ex = { null };
		mb.forEach((bs, off, len, ret) -> {
			try {
				zip.write(bs, off, len);
			} catch (Exception e) {
				ex[0] = e;
				return -1;
			}
			return 0;
		});
		if (ex[0] != null) throw ex[0];
	}
}
